"""
Case Type   : spq多机并行
Case Name   : 验证兼容A、B、C、PG模式数据库是否支持spq多机执行流程
Create At   : 2024/04/08
Owner       : songjing20
Description :
    1.创建兼容库
    2.连接兼容库建表查询是否走多机流程
    3.清理环境
Expect      :
    1.成功
    2.成功，多机并行
    3.成功
History     :
"""

import os
import unittest

from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant


class SpqPlugin(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.db_name = {'db_spq_0046_a': 'A', 'db_spq_0046_b': 'B',
                        'db_spq_0046_c': 'C', 'db_spq_0046_pg': 'PG'}
        self.tb_name = 't_spq_0046'

    def test_delete(self):
        self.log.info('-----step1:创建兼容库;step2:连接兼容库建表查询是否走多机流程-----')
        sql = f'''set spqplugin.enable_spq = on;
        drop table if exists {self.tb_name} cascade;
        create table {self.tb_name}(
        id        serial primary key,
        name      varchar(20) not null,
        age       tinyint default 0,
        height    decimal(4,2),
        gender    char(10) default 'null',
        cls_id    int default 0);
        insert into {self.tb_name} values(1, 'LiMing', 12, 1.58, '男', 1),
        (2, 'Danny', 11, 1.46, default, 1), (3, '张三', 14, 1.61, '男', 1),
        (4, 'xiaohua', 13, 1.58, '女', 2), (5, 'xiaohei', 12, 1.58, '女', 2),
        (6, '赵钱', 18, 1.78, default, 3), (7, 'LiYi', 17, 1.80, '男', 2);
        analyze {self.tb_name};
        explain select id, name, age, height, gender, cls_id
        from {self.tb_name} where id = 5;
        explain select * from {self.tb_name} order by id desc limit 2;
        select id, name, age, height, gender, cls_id
        from {self.tb_name} where id = 5;
        select * from {self.tb_name} order by id desc limit 2;
        drop table if exists {self.tb_name} cascade;'''
        for dbname, mode in self.db_name.items():
            self.log.info(f'----- 创建兼容{mode}库-----')
            create_db_res = self.pri_sh.execut_db_sql(
                f"drop database if exists {dbname};"
                f"create database {dbname} dbcompatibility '{mode}';")
            self.log.info(create_db_res)
            self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, create_db_res,
                          "建库失败")
            self.log.info(f'----- 连接兼容{mode}库建表查询是否走多机流程 -----')
            sql_res = self.pri_sh.execut_db_sql(sql, dbname=dbname)
            self.log.info(sql_res)
            self.assertIn(self.constant.CREATE_TABLE_SUCCESS, sql_res,
                          '创建表失败')
            self.assertIn(self.constant.INSERT_SUCCESS_MSG, sql_res, 'insert失败')
            self.assertIn(self.constant.ANALYZE_SUCCESS_MSG,
                          sql_res, 'insert失败')
            self.assertIn(f'Index Scan using {self.tb_name}_pkey '
                          f'on {self.tb_name}', sql_res, '查询失败')
            self.assertIn('Seq Scan', sql_res, '查询失败')
            self.assertIn('5 | xiaohei | 12  |   1.58 | 女        |      2',
                          sql_res, '查询失败')
            self.assertIn('7 | LiYi   | 17  |   1.80 | 男        |      2\n'
                          '  6 | 赵钱 | 18  |   1.78 | null       |      3\n'
                          '(2 rows)', sql_res, '查询失败')

    def tearDown(self):
        text = '-----step3:清理环境;expect:成功-----'
        self.log.info(text)
        drop_res = []
        for dbname in self.db_name.keys():
            res = self.pri_sh.execut_db_sql(f"drop database {dbname};")
            self.log.info(res)
            drop_res.append(res)
        self.assertEqual(drop_res.count(self.constant.DROP_DATABASE_SUCCESS),
                         4, f'删库失败: {text}')
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
